// Package repository implements database access for number package management.
package repository

import (
	"context"
	"database/sql"

	"github.com/we-pay-kit/we-pay-kit/internal/db"
)

type NumberPackageRepository struct{ db *db.DB }

func NewNumberPackageRepository(database *db.DB) *NumberPackageRepository {
	return &NumberPackageRepository{db: database}
}

func (r *NumberPackageRepository) Create(ctx context.Context, np *NumberPackage) (int64, error) {
	q := `INSERT INTO number_package(package_name, total_count, uploaded_count, used_count, upload_time, status, created_by)
          VALUES($1,$2,$3,$4,$5,$6,$7) RETURNING id`
	var id int64
	err := r.db.QueryRowContext(ctx, q, np.PackageName, np.TotalCount, np.UploadedCount, np.UsedCount, np.UploadTime, np.Status, np.CreatedBy).Scan(&id)
	return id, err
}

func (r *NumberPackageRepository) UpdateStatus(ctx context.Context, id int64, status string) error {
	q := `UPDATE number_package SET status=$1 WHERE id=$2`
	_, err := r.db.ExecContext(ctx, q, status, id)
	return err
}

func (r *NumberPackageRepository) GetByID(ctx context.Context, id int64) (*NumberPackage, error) {
	q := `SELECT id, package_name, total_count, uploaded_count, used_count, upload_time, status, created_by, created_at
          FROM number_package WHERE id=$1`
	row := r.db.QueryRowContext(ctx, q, id)
	var np NumberPackage
	var ut sql.NullTime
	if err := row.Scan(&np.ID, &np.PackageName, &np.TotalCount, &np.UploadedCount, &np.UsedCount, &ut, &np.Status, &np.CreatedBy, &np.CreatedAt); err != nil {
		return nil, err
	}
	if ut.Valid {
		t := ut.Time
		np.UploadTime = &t
	}
	return &np, nil
}

func (r *NumberPackageRepository) List(ctx context.Context, limit, offset int) ([]NumberPackage, error) {
	if limit <= 0 {
		limit = 20
	}
	q := `SELECT id, package_name, total_count, uploaded_count, used_count, upload_time, status, created_by, created_at
          FROM number_package ORDER BY id DESC LIMIT $1 OFFSET $2`
	rows, err := r.db.QueryContext(ctx, q, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	res := make([]NumberPackage, 0, limit)
	for rows.Next() {
		var np NumberPackage
		var ut sql.NullTime
		if err := rows.Scan(&np.ID, &np.PackageName, &np.TotalCount, &np.UploadedCount, &np.UsedCount, &ut, &np.Status, &np.CreatedBy, &np.CreatedAt); err != nil {
			return nil, err
		}
		if ut.Valid {
			t := ut.Time
			np.UploadTime = &t
		}
		res = append(res, np)
	}
	return res, rows.Err()
}

// Detail repository
type NumberPackageDetailRepository struct{ db *db.DB }

func NewNumberPackageDetailRepository(database *db.DB) *NumberPackageDetailRepository {
	return &NumberPackageDetailRepository{db: database}
}

func (r *NumberPackageDetailRepository) Add(ctx context.Context, d *NumberPackageDetail) (int64, error) {
	q := `INSERT INTO number_package_detail(package_id, phone_number, user_id, status)
          VALUES($1,$2,$3,$4) RETURNING id`
	var id int64
	err := r.db.QueryRowContext(ctx, q, d.PackageID, d.PhoneNumber, d.UserID, d.Status).Scan(&id)
	return id, err
}

func (r *NumberPackageDetailRepository) MarkUsed(ctx context.Context, packageID int64, phone string) error {
	q := `UPDATE number_package_detail SET status='USED' WHERE package_id=$1 AND phone_number=$2`
	_, err := r.db.ExecContext(ctx, q, packageID, phone)
	return err
}

func (r *NumberPackageDetailRepository) ListByPackage(ctx context.Context, packageID int64, limit, offset int) ([]NumberPackageDetail, error) {
	if limit <= 0 {
		limit = 100
	}
	q := `SELECT id, package_id, phone_number, user_id, status, created_at
          FROM number_package_detail WHERE package_id=$1 ORDER BY id ASC LIMIT $2 OFFSET $3`
	rows, err := r.db.QueryContext(ctx, q, packageID, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	res := make([]NumberPackageDetail, 0, limit)
	for rows.Next() {
		var d NumberPackageDetail
		var uid sql.NullString
		if err := rows.Scan(&d.ID, &d.PackageID, &d.PhoneNumber, &uid, &d.Status, &d.CreatedAt); err != nil {
			return nil, err
		}
		if uid.Valid {
			d.UserID = &uid.String
		}
		res = append(res, d)
	}
	return res, rows.Err()
}

// Delete removes a number package by id
func (r *NumberPackageRepository) Delete(ctx context.Context, id int64) error {
	q := `DELETE FROM number_package WHERE id=$1`
	_, err := r.db.ExecContext(ctx, q, id)
	return err
}
